home comics writing pictures archive about

ServerSelectorViewModel.cs

Language: C#
Last Modified: 2020-06-27 1:58:36 PM UTC
File Size: 9319 bytes
http://www.penguinstew.ca/example/dataviewer/ViewModel/ServerSelectorViewModel.cs
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Input;
using System.Windows.Threading;
using Penguin.MVVMBase.Util;
using Penguin.MVVMBase.ViewModelBase;
namespace DataViewer.ViewModel
{
class ServerSelectorViewModel : DialogViewModelBase
{
#region Variables
/// <summary>
/// Stores a reference to the dispatcher
/// </summary>
private Dispatcher m_dispatcher;
#endregion
#region Properties
private RelayCommand m_okCommand;
/// <summary>
/// Command called when Ok button is pressed
/// </summary>
public ICommand OkCommand
{
get
{
return m_okCommand;
}
}
private RelayCommand m_cancelCommand;
/// <summary>
/// Command called when Cancel button is pressed
/// </summary>
public ICommand CancelCommand
{
get
{
return m_cancelCommand;
}
}
private ObservableCollection<String> m_servers;
/// <summary>
/// List of available servers retrieved through a call to SqlDataSourceEnumerator.GetDataSources
/// </summary>
public ObservableCollection<String> Servers
{
get
{
return m_servers;
}
set
{
SetProperty(ref m_servers, value, "Servers");
}
}
private string m_selectedServer;
/// <summary>
/// The currently selected server
/// </summary>
public string SelectedServer
{
get
{
return m_selectedServer;
}
set
{
//Load database list if selection changes
if (SetProperty(ref m_selectedServer, value, "SelectedServer"))
{
IsLoading = true;
Action loadServersAction = new Action(LoadDatabases);
loadServersAction.BeginInvoke(null, null);
}
}
}
private ObservableCollection<String> m_databases;
/// <summary>
/// List of available databases retrieved through a call to sp_databases on the currently selected server
/// </summary>
public ObservableCollection<String> Databases
{
get
{
return m_databases;
}
set
{
SetProperty(ref m_databases, value, "Databases");
}
}
private string m_selectedDatabase;
/// <summary>
/// The currently selected database
/// </summary>
public string SelectedDatabase
{
get
{
return m_selectedDatabase;
}
set
{
SetProperty(ref m_selectedDatabase, value, "SelectedDatabase");
}
}
private bool m_isLoading;
/// <summary>
/// Indicates that the window is in a state of loading
/// </summary>
public bool IsLoading
{
get
{
return m_isLoading;
}
set
{
SetProperty(ref m_isLoading, value, "IsLoading");
}
}
private bool m_isNewDatabase;
/// <summary>
/// Indicates that a new database is being created
/// </summary>
public bool IsNewDatabase
{
get
{
return m_isNewDatabase;
}
set
{
SetProperty(ref m_isNewDatabase, value, "IsNewDatabase");
}
}
private bool m_isServerSelected;
/// <summary>
/// Indicates if a server is selected
/// </summary>
public bool IsServerSelected
{
get
{
return m_isServerSelected;
}
set
{
SetProperty(ref m_isServerSelected, value, "IsServerSelected");
}
}
private string m_newDatabaseName;
/// <summary>
/// The name of the new database to be created
/// </summary>
public string NewDatabaseName
{
get
{
return m_newDatabaseName;
}
set
{
SetProperty(ref m_newDatabaseName, value, "NewDatabaseName");
}
}
#endregion
#region Constructor
public ServerSelectorViewModel()
{
IsLoading = true;
//Get a reference to the current dispatcher
m_dispatcher = Dispatcher.CurrentDispatcher;
//Setup commands
m_okCommand = new RelayCommand(
_ => {
if (String.IsNullOrEmpty(SelectedServer))
{
RequestMsgBox("Please select a server");
}
else if (!IsNewDatabase && String.IsNullOrEmpty(SelectedDatabase))
{
RequestMsgBox("Please select a database");
}
else if (IsNewDatabase && String.IsNullOrWhiteSpace(NewDatabaseName))
{
RequestMsgBox("Please enter a database name");
}
else
{
RequestClose(true);
}
},
_ => {
return !IsLoading;
});
m_cancelCommand = new RelayCommand(
_ =>
{
RequestClose(false);
});
//Start loading server list
Action loadServersAction = new Action(loadServers);
loadServersAction.BeginInvoke(null, null);
}
#endregion
#region Private Methods
/// <summary>
/// Uses the DataSourceEnumerator to get the list of local servers
/// </summary>
private void loadServers()
{
ObservableCollection<String> list = new ObservableCollection<string>();
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
DataTable table = instance.GetDataSources();
//Combine the serverName and InstanceName to get the source
foreach (DataRow row in table.Select())
{
String source = row["ServerName"].ToString() + "\\" + row["InstanceName"].ToString();
list.Add(source);
}
//Use the dispatcher to set the list and end loading
m_dispatcher.Invoke(new Action(() =>
{
Servers = list;
IsLoading = false;
}));
}
/// <summary>
/// Queries the selected server to get list of databases
/// </summary>
private void LoadDatabases()
{
if (SelectedServer == null)
return;
SqlConnection dataConnection = new SqlConnection();
//Setup connection string from the selected server
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = SelectedServer;
builder.IntegratedSecurity = true;
dataConnection.ConnectionString = builder.ConnectionString;
dataConnection.Open();
//Call the databases special procedure to get list of database
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.CommandText = "sp_databases";
SqlDataReader dataReader = dataCommand.ExecuteReader();
ObservableCollection<String> list = new ObservableCollection<string>();
while (dataReader.Read())
{
string name = dataReader.GetString(0);
//Ignore system databases
if (name.Equals("master") || name.Equals("model") || name.Equals("tempdb") || name.Equals("msdb"))
{
continue;
}
list.Add(name);
}
//Use the dispatcher to set the list and end loading
m_dispatcher.Invoke(new Action(() =>
{
Databases = list;
IsLoading = false;
}));
dataReader.Close();
dataConnection.Close();
IsServerSelected = true;
}
#endregion
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318